﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using deviceManage.Models;
using deviceManage.vo;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using deviceManage.Filters;
using deviceManage.Tools;
using deviceManage.VO;
using deviceManage.Service;
using System.IO;
using OfficeOpenXml;
using Microsoft.AspNetCore.Hosting;
using OfficeOpenXml.Drawing;
using System.Text;
using System.Globalization;

namespace deviceManage.Controllers
{
    /// <summary>
    /// 设备台账信息控制器
    /// </summary>
    [CheckLoginAttribute]
    public class DeviceInfoController : Controller
    {
        private readonly DeviceCtx _context;
        private DeviceService deviceService;
        private IHostingEnvironment _hostingEnvironment;

        public DeviceInfoController(DeviceCtx context, IHostingEnvironment hostingEnvironment)
        {
            _context = context;
            _hostingEnvironment = hostingEnvironment;
            deviceService = new DeviceService(_context);
        }

        public IActionResult Index()
        {
            //获取设备分类，用于下拉显示
            var typeList = _context.DeviceType.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();

            //是否只读权限
            int? flag = HttpContext.Session.GetInt32("viewFlag");

            return View(Tuple.Create(typeList,flag));
        }


        //ajax 获取分页数据,在建项目
        public AjaxResult List(int page, int limit, String name)
        {
            AjaxResult r = new AjaxResult();
            IQueryable<Device> query = _context.Device;
            if (!string.IsNullOrWhiteSpace(name))
            {
                query = query.Where(p => p.Name.Contains(name));
            }

            if (!string.IsNullOrWhiteSpace(Request.Form["deviceTypeId"]))
            {
                query = query.Where(p => p.DeviceTypeId == Request.Form["deviceTypeId"]);
            }

            if (!string.IsNullOrWhiteSpace(Request.Form["deviceId"]))
            {
                query = query.Where(p => p.DeviceId == Request.Form["deviceId"]);
            }
            if (!string.IsNullOrWhiteSpace(Request.Form["start"]))
            {
                DateTime start = DateTime.Parse(Request.Form["start"]);
                query = query.Where(p => p.BuyDate >= start);
            }
            if (!string.IsNullOrWhiteSpace(Request.Form["end"]))
            {
                DateTime end = DateTime.Parse(Request.Form["end"]);
                query = query.Where(p => p.BuyDate <= end);
            }
            if (!string.IsNullOrWhiteSpace(Request.Form["no"]))
            {
                query = query.Where(p => p.No == Request.Form["no"]);
            }
            

            r.Count = query.Count();
            r.Code = 0;
            try
            {
                r.Data = query.Include(p => p.DeviceType).Include(p => p.Room)
                .Include(p=>p.DeviceType.DeviceTypeCategory)
                .OrderBy(p=>p.Status)
                .OrderBy(p =>p.Name)
                .Skip((page - 1) * limit).Take(limit)
                .ToList();
            }
            catch (Exception e)
            {
                r.Msg = e.Message;
            }
            return r;
        }

        /// <summary>
        /// 显示设备入库页面
        /// </summary>
        /// <returns></returns>
        public IActionResult DeviceIn(string id)
        {
            //获取设备分类，用于下拉显示
            var typeList = _context.DeviceType.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            //部门
            var departList = _context.Depart.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            //机房
            var roomList = _context.Room.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            //项目
            var projectList = _context.Project.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            //服务合同
            var buyContractList = _context.BuyContract.Where(p => p.Deleted == 0).ToList();
            //采购合同
            var serviceContractList = _context.ServiceContract.Where(p => p.Deleted == 0).ToList();
            //供应商
            var supplierList = _context.Supplier.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            //运维计划
            var planList = _context.DevicePlan.OrderByDescending(p => p.EndTime);
            //设备类别分类
            var categoryList = _context.DeviceTypeCategory.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();

            //获取model
            Device device = new Device();
            if (!string.IsNullOrWhiteSpace(id))
            {
                device = _context.Device.Single(p => p.Id == id);
                device.IsEdit = true;
            }
            else //自动生成ID
            {
                device.Id = System.Guid.NewGuid().ToString();
                device.IsEdit = false;
            }

            return View(Tuple.Create(device, typeList, departList, roomList,
                projectList, buyContractList, Tuple.Create(serviceContractList, supplierList, categoryList)));
            
        }


        /// <summary>
        /// 显示设备入库页面
        /// </summary>
        /// <returns></returns>
        public IActionResult Copy(string id)
        {
            //获取设备分类，用于下拉显示
            var typeList = _context.DeviceType.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            //部门
            var departList = _context.Depart.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            //机房
            var roomList = _context.Room.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            //项目
            var projectList = _context.Project.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            //服务合同
            var buyContractList = _context.BuyContract.Where(p => p.Deleted == 0).ToList();
            //采购合同
            var serviceContractList = _context.ServiceContract.Where(p => p.Deleted == 0).ToList();
            var supplierList = _context.Supplier.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();

            //复制设备照片和附件
            string newId = System.Guid.NewGuid().ToString();
            var doctList = _context.Document.Where(p => p.ObjectId == id).ToList();
            foreach (Document doc in doctList)
            {
                Document newDoc = new Document();
                newDoc.ObjectId = newId;
                newDoc.FilePath = doc.FilePath;
                newDoc.Category = doc.Category;
                newDoc.Name = doc.Name;
                newDoc.UploadTime = doc.UploadTime;
                _context.Add(newDoc);
            }
            _context.SaveChanges();

            //获取model
            Device copyDevice = _context.Device.Single(p => p.Id == id);
            copyDevice.Id = newId;
            copyDevice.DeviceId = "";
            copyDevice.No = "";
            copyDevice.IsEdit = false;

            return View("DeviceIn", Tuple.Create(copyDevice, typeList, departList, roomList,
                projectList, buyContractList, serviceContractList, supplierList));
        }



        /// <summary>
        /// 保存设备信息
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        [HttpPost]
        public AjaxResult Save(Device obj)
        {
            //校验设备编码
            var exist = _context.Device.Any(p => p.Id != obj.Id && p.DeviceId == obj.DeviceId);
            if (exist)
            {
                return AjaxResult.GetError("设备编码已经存在");
            }
            //固定资产编号
            exist = _context.Device.Any(p => p.Id != obj.Id && p.No == obj.No);
            if (exist)
            {
                return AjaxResult.GetError("固定资产编号已经");
            }

            exist = _context.Device.Any(p => p.Id == obj.Id);
            if (obj.Id == null || !exist)
            { //新增          
                obj.Status = DeviceState.STATE_NORMAL;
                obj.Deleted = 0;
                _context.Add(obj);
                _context.SaveChanges();
                return AjaxResult.GetOK("保存成功");
            }
            else
            {
                Device device = _context.Device.Single(p => p.Id == obj.Id);
                device.BuyContractId = obj.BuyContractId;
                device.BuyDate = obj.BuyDate;
                device.Name = obj.Name;
                device.No = obj.No;
                device.owner_unit = obj.owner_unit;
                device.DeviceTypeId = obj.DeviceTypeId;
                device.Os = obj.Os;
                device.Remark = obj.Remark;
                device.RoomId = obj.RoomId;
                device.ServiceDate = obj.ServiceDate;
                device.ReceiveDate = obj.ReceiveDate;
                device.DeviceId = obj.DeviceId;
                device.IsDomestic = obj.IsDomestic;
                device.DepartId = obj.DepartId;
                device.Model = obj.Model;
                device.Location = obj.Location;
                this._context.SaveChanges(); //保存
                return AjaxResult.GetOK("保存成功");
            }
        }

        /// <summary>
        /// 显示维修页面
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IActionResult GoRepair(string id)
        {
            Device device = _context.Device.Single(p => p.Id == id);
            return View("Repair", device);
        }

        /// <summary>
        /// 保存维修记录
        /// </summary>
        /// <param name="log"></param>
        /// <returns></returns>
        public AjaxResult SaveRepair(RepairLog log)
        {
            log.CreateDate = DateTime.Now;
            log.CreateUserId = HttpContext.Session.GetString(WebContext.SESSION_KEY_USER_ID);
            log.Deleted = 0;
            log.State = 1; //维修中
            _context.RepairLog.Add(log);
            _context.SaveChanges();

            //更新设备状态
            Device device = _context.Device.Single(p => p.Id == log.DeviceId);
            device.Status = DeviceState.STATE_REPAIR;
            _context.Device.Update(device);
            _context.SaveChanges();

            return AjaxResult.GetOK("保存成功！");
        }

        /// <summary>
        /// 维修完成
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IActionResult FinishRepair(string id)
        {
            Device device = _context.Device.Single(p => p.Id == id);
            return View("FinishRepair", device);
        }
        //保存维修完成的日期
        public AjaxResult SaveFinishRepair(RepairLog log)
        {


            //更新设备状态
            Device device = _context.Device.Single(p => p.Id == log.DeviceId);
            device.Status = DeviceState.STATE_NORMAL;
            _context.Device.Update(device);
            _context.SaveChanges();

            //更新维修记录单
            RepairLog target = _context.RepairLog.Single(p => p.State == 1 && p.DeviceId == log.DeviceId);
            target.State = 2; //维修完成
            target.EndDate = log.EndDate;
            _context.RepairLog.Update(target);
            _context.SaveChanges();

            return AjaxResult.GetOK("保存成功！");
        }


        /// <summary>
        /// 进入报废页面
        /// </summary>
        /// <param name="id">设备ID</param>
        /// <returns></returns>
        public IActionResult Scrap(string id)
        {
            Device device = _context.Device.Single(p => p.Id == id);
            return View(device);
        }
        //保存报废信息
        public AjaxResult SaveScrap(ScrapLog log)
        {


            //更新设备状态
            Device device = _context.Device.Single(p => p.Id == log.DeviceId);
            device.Status = DeviceState.STATE_SCRAP;
            _context.Device.Update(device);
            _context.SaveChanges();

            //保存报废单
            log.CreateDate = DateTime.Now;
            log.CreateUserId = HttpContext.Session.GetString(WebContext.SESSION_KEY_USER_ID);
            _context.ScrapLog.Add(log);
            _context.SaveChanges();

            return AjaxResult.GetOK("保存成功！");
        }


        /// <summary>
        /// 设备详情查看
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IActionResult Detail(string id)
        {
            Device device = _context.Device
                .Include(p => p.Depart)
                .Include(p => p.Room)
                .Include(p => p.Project)
                .Include(p => p.BuyContract)
                .Include(p => p.Supplier)
                //.Include(p => p.ServiceContract)
                .Include(p => p.DeviceType)
                .Single(p => p.Id == id);

            ScrapLog scrapLog = _context.ScrapLog.SingleOrDefault(p => p.DeviceId == id);
            return View(Tuple.Create(device, scrapLog));
        }


        /// <summary>
        /// 获取设备维修记录，ajax
        /// </summary>
        /// <param name="id">设备id</param>
        /// <returns></returns>
        public AjaxResult RepairLog(string id)
        {
            AjaxResult r = new AjaxResult();
            IQueryable<RepairLog> query = _context.RepairLog.Where(p => p.DeviceId == id);
            r.Count = query.Count();
            r.Code = 0;
            try
            {
                r.Data = query.OrderBy(p => p.BeginDate).ToList();
            }
            catch (Exception e)
            {
                r.Msg = e.Message;
            }
            return r;
        }


        /// <summary>
        /// 设备维修记录主页
        /// </summary>
        /// <param name="id">设备id</param>
        /// <returns></returns>
        public IActionResult RepairLogIndex(string id)
        {

            //获取设备分类，用于下拉显示
            var typeList = _context.DeviceType.Where(p => p.Deleted == 0).OrderBy(p => p.SortOrder).ToList();
            int? flag = HttpContext.Session.GetInt32("viewFlag");
            return View(Tuple.Create(typeList,flag));
        }

        /// <summary>
        /// 获取所有设备维修记录，ajax
        /// </summary>
        /// <param name="id">设备id</param>
        /// <returns></returns>
        public AjaxResult RepairLogAll(int page, int limit, string name)
        {
            AjaxResult r = new AjaxResult();
            IQueryable<RepairLog> query = _context.RepairLog;

            if (!string.IsNullOrWhiteSpace(name))
            {
                query = query.Where(p => p.Device.Name.Contains(name));
            }

            if (!string.IsNullOrWhiteSpace(Request.Form["deviceTypeId"]))
            {
                query = query.Where(p => p.Device.DeviceTypeId == Request.Form["deviceTypeId"]);
            }

            if (!string.IsNullOrWhiteSpace(Request.Form["deviceId"]))
            {
                query = query.Where(p => p.Device.DeviceId == Request.Form["deviceId"]);
            }
            if (!string.IsNullOrWhiteSpace(Request.Form["start"]))
            {
                DateTime start = DateTime.Parse(Request.Form["start"]);
                query = query.Where(p => p.BeginDate >= start);
            }
            if (!string.IsNullOrWhiteSpace(Request.Form["end"]))
            {
                DateTime end = DateTime.Parse(Request.Form["end"]);
                query = query.Where(p => p.EndDate <= end);
            }


            r.Count = query.Count();
            r.Code = 0;
            try
            {
                r.Data = query.Include(p => p.Device).ThenInclude(p => p.DeviceType).OrderByDescending(p => p.BeginDate)
                    .Skip((page - 1) * limit).Take(limit)
                    .ToList();
            }
            catch (Exception e)
            {
                r.Msg = e.Message;
            }
            return r;
        }

        /// <summary>
        /// 进入添加维修记录页面
        /// </summary>
        /// <returns></returns>
        public IActionResult GoAddRepairLog()
        {
            List<Device> list = _context.Device.Where(p => p.Deleted == 0).OrderBy(p => p.Name).ToList();
            return View(list);
        }

        /// <summary>
        /// 添加维修记录
        /// </summary>
        /// <param name="log"></param>
        /// <returns></returns>
        public AjaxResult AddRepairLog(RepairLog log)
        {
            log.CreateDate = DateTime.Now;
            log.CreateUserId = HttpContext.Session.GetString(WebContext.SESSION_KEY_USER_ID);
            log.State = 2;
            _context.RepairLog.Add(log);
            _context.SaveChanges();
            return AjaxResult.GetOK("添加成功！");
        }

        /// <summary>
        /// 删除维修记录
        /// </summary>
        /// <param name="log"></param>
        /// <returns></returns>
        public AjaxResult DeleteRepairLog(string id)
        {
            var log = _context.RepairLog.Single(p => p.Id == id);
            if (log.State == 1)
            {
                return AjaxResult.GetError("当前设备正在维修中，无法删除维修记录！");
            }
            _context.RepairLog.Remove(log);
            _context.SaveChanges();
            return AjaxResult.GetOK("删除成功！");
        }

        /// <summary>
        /// 删除设备
        /// </summary>        
        public AjaxResult DeleteDevice(string id)
        {
            this.deviceService.Delete(id);
            return AjaxResult.GetOK("删除成功！");
        }

        public AjaxResult RevertDevice(string id)
        {
            this.deviceService.Revert(id);
            return AjaxResult.GetOK("恢复成功！");
        }


        /// <summary>
        /// 公用路径
        /// </summary>
        /// <returns></returns>
        private Tuple<string, string> GetTuple()
        {
            string sWebRootFolder = _hostingEnvironment.WebRootPath + "/tmp";
            if (Directory.Exists(sWebRootFolder) == false)//如果不存在就创建file文件夹
            {
                Directory.CreateDirectory(sWebRootFolder);
            }

            //string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = $"{DateTime.Now.ToString("yyyyMMddHHmmssfff")}-{Guid.NewGuid()}.xlsx";
            return Tuple.Create(sWebRootFolder, sFileName);
        }

        public IActionResult Export()
        {
            string sWebRootFolder = GetTuple().Item1;
            string sFileName = GetTuple().Item2;
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
                //添加头
                int headerCol = 1;
                worksheet.Cells[1, headerCol++].Value = "设备名称";
                worksheet.Cells[1, headerCol++].Value = "设备类型";
                worksheet.Cells[1, headerCol++].Value = "规格型号";
                worksheet.Cells[1, headerCol++].Value = "设备编号";
                worksheet.Cells[1, headerCol++].Value = "固定资产编号";
                worksheet.Cells[1, headerCol++].Value = "所在机房";
                worksheet.Cells[1, headerCol++].Value = "摆放位置";
                worksheet.Cells[1, headerCol++].Value = "所属项目";
                worksheet.Cells[1, headerCol++].Value = "IP地址";
                worksheet.Cells[1, headerCol++].Value = "供应商";
                worksheet.Cells[1, headerCol++].Value = "所属部门";
                worksheet.Cells[1, headerCol++].Value = "操作系统";                
                worksheet.Cells[1, headerCol++].Value = "备注信息";
                worksheet.Cells[1, headerCol++].Value = "状态";
                worksheet.Cells[1, headerCol++].Value = "采购日期";
                worksheet.Cells[1, headerCol++].Value = "接管日期";
                worksheet.Cells[1, headerCol++].Value = "出保日期";
                // worksheet.Cells[1, headerCol++].Value = "设备图片1";
                // worksheet.Cells[1, headerCol++].Value = "设备图片2";
                //worksheet.Cells[1, headerCol++].Value = "设备图片3";

                //设置宽度
                for (int i = 1; i < headerCol; i++) {
                    worksheet.Column(i).Width = 30;
                } 

                //添加值
                var list = _context.Device.Include(p=>p.DeviceType)
                    .Include(p=>p.Room)
                    .OrderBy(p => p.Status)
                    .OrderBy(p => p.Name).ToList();

                int row = 2;
                foreach(var device in list)
                {
                    int col = 1;
                    worksheet.Cells[row, col].Value = device.Name;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    worksheet.Cells[row, col].Value = device.DeviceType.Name;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    worksheet.Cells[row, col].Value = device.Model;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    worksheet.Cells[row, col].Value = device.DeviceId;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    
                    worksheet.Cells[row, col].Value = device.No;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    worksheet.Cells[row, col].Value = device.Room.Name;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    worksheet.Cells[row, col].Value = device.Location;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    if(device.Project != null)
                    {
                        worksheet.Cells[row, col].Value = device.Project.Name;
                        worksheet.Cells[row, col].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    }
                    col++;

                    worksheet.Cells[row, col].Value = device.Ip;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    if (device.Supplier != null)
                    {
                        worksheet.Cells[row, col].Value = device.Supplier.Name;
                        worksheet.Cells[row, col].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    }
                    col++;

                    if (device.Depart != null)
                    {
                        worksheet.Cells[row, col].Value = device.Depart.Name;
                        worksheet.Cells[row, col].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    }
                    col++;

                    worksheet.Cells[row, col].Value = device.Os;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    worksheet.Cells[row, col].Value = device.Remark;
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    worksheet.Cells[row, col].Value = getState(device.Status);
                    worksheet.Cells[row, col++].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    if (device.BuyDate != null)
                    {
                        worksheet.Cells[row, col].Value = device.BuyDate.Value.ToString("yyyy-MM-dd");
                        worksheet.Cells[row, col].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    }
                    col++;

                    if (device.ReceiveDate != null)
                    {
                        worksheet.Cells[row, col].Value = device.ReceiveDate.Value.ToString("yyyy-MM-dd");
                        worksheet.Cells[row, col].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    }
                    col++;
                    if (device.ServiceDate != null)
                    {
                        worksheet.Cells[row, col].Value = device.ServiceDate.Value.ToString("yyyy-MM-dd");
                        worksheet.Cells[row, col].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    }
                    col++;


                    //下载图片
                    /* 
                    var imgList = _context.Document.Where(p => p.ObjectId == device.Id && p.Category == 1).ToList();                    
                    foreach(var img in imgList)
                    {
                        worksheet.Row(row).Height = 100;
                        worksheet.Column(col).Width = 25;

                        FileInfo fileimage = new FileInfo(img.FilePath);
                        ExcelPicture pic = worksheet.Drawings.AddPicture(System.Guid.NewGuid().ToString(), fileimage);
                        pic.SetSize(95, 95);
                        pic.SetPosition(row-1, 1, col-1, 1);                        
                        pic.Locked = true;              
                        col++;
                    } */

                    row++;
                }

                package.Save();
            }
            return File("tmp/"+sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","设备导出.xlsx");
        }

        /// <summary>
        /// 导入设备信息
        /// </summary>
        /// <param name="excelfile"></param>
        /// <returns></returns>
        [HttpPost]
        public AjaxResult ImportExcel(IFormFile file)
        {
            if(file == null)
            {
                return AjaxResult.GetError("上传的文件不能为空");
            }
            if (!file.FileName.EndsWith(".xlsx"))
            {
                return AjaxResult.GetError("上传的文件必须为Excel文件（xlsx)");
            }

            DateTimeFormatInfo format = new System.Globalization.DateTimeFormatInfo();
            format.ShortDatePattern = "yyyy-MM-dd";

            string sWebRootFolder = GetTuple().Item1;
            string sFileName = GetTuple().Item2;
            FileInfo t = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            try
            {
                using (FileStream fs = new FileStream(t.ToString(), FileMode.Create))
                {
                    file.CopyTo(fs);
                    fs.Flush();
                }
                using (ExcelPackage package = new ExcelPackage(t))
                {

                    
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    int rowCount = worksheet.Dimension.Rows;
                    int ColCount = worksheet.Dimension.Columns;
                    
                    var picList = worksheet.Drawings.ToList();

                    for (int row =2; row <= rowCount; row++)
                    {
                        Device device = new Device();

                        device.Name = GetValueString(worksheet.Cells[row, 1].Value);
                        string typeName = GetValueString(worksheet.Cells[row, 2].Value);
                        device.DeviceTypeId = GetDeviceTypeId(typeName);
                        device.Model = GetValueString(worksheet.Cells[row, 3].Value);
                        device.DeviceId = GetValueString(worksheet.Cells[row, 4].Value);
                        device.No = GetValueString(worksheet.Cells[row, 5].Value) ;
                        device.RoomId = GetRoomId(GetValueString(worksheet.Cells[row, 6].Value));
                        device.Status = DeviceState.STATE_NORMAL;

                        if(worksheet.Cells[row, 7].Value != null)
                        {
                            device.BuyDate = Convert.ToDateTime(worksheet.Cells[row, 7].Value, format);
                        }
                        
                        if(worksheet.Cells[row, 8].Value != null){
                            device.ReceiveDate = Convert.ToDateTime(worksheet.Cells[row, 8].Value, format);
                        }
                        
                        if(worksheet.Cells[row, 9].Value != null)
                        {
                            device.ServiceDate = Convert.ToDateTime(worksheet.Cells[row, 9].Value, format);
                        }
                        


                        device.Location = GetValueString(worksheet.Cells[row, 10].Value);
                        device.DepartId = GetDepartId(GetValueString(worksheet.Cells[row, 11].Value));
                        device.SupplierId = GetSupplierId(GetValueString(worksheet.Cells[row, 12].Value));
                        device.Ip = GetValueString(worksheet.Cells[row, 13].Value);
                        device.Os = GetValueString(worksheet.Cells[row, 14].Value);

                        _context.Device.Add(device);

                    }
                    _context.SaveChanges();
                    return AjaxResult.GetOK("导入成功！");
                }
            }
            catch (Exception ex)
            {
                return AjaxResult.GetError(ex.Message);
            }
        }

        private string GetSupplierId(string name)
        {
            var list = _context.Supplier.Where(p => p.Name == name).ToList();
            if (list.Count > 0)
            {
                return list[0].Id;
            }
            else
            {
                Supplier deviceType = new Supplier();
                deviceType.Name = name;
                deviceType.Deleted = 0;
                deviceType.SortOrder = 100;
                _context.Supplier.Add(deviceType);
                _context.SaveChanges();
                return deviceType.Id;
            }
        }

        private String GetValueString(Object obj)
        {
            if(obj == null)
            {
                return "";
            }
            return obj.ToString();
        }

        private string GetDepartId(string name)
        {
            var list = _context.Depart.Where(p => p.Name == name).ToList();
            if (list.Count > 0)
            {
                return list[0].Id;
            }
            else
            {
                Depart deviceType = new Depart();
                deviceType.Name = name;
                deviceType.Deleted = 0;
                deviceType.SortOrder = 100;
                _context.Depart.Add(deviceType);
                _context.SaveChanges();
                return deviceType.Id;
            }
        }

        private string GetRoomId(string name)
        {
            var list = _context.Room.Where(p => p.Name == name).ToList();
            if (list.Count > 0)
            {
                return list[0].Id;
            }
            else
            {
                Room deviceType = new Room();
                deviceType.Name = name;
                deviceType.Deleted = 0;
                deviceType.SortOrder = 100;
                _context.Room.Add(deviceType);
                _context.SaveChanges();
                return deviceType.Id;
            }
        }
 
        private string GetDeviceTypeId(string type)
        {
            var list = _context.DeviceType.Where(p => p.Name == type).ToList();
            if(list.Count > 0)
            {
                return list[0].Id;
            }
            else
            {
                DeviceType deviceType = new DeviceType();
                deviceType.Name = type;
                deviceType.Deleted = 0;
                deviceType.SortOrder = 100;
                _context.DeviceType.Add(deviceType);
                _context.SaveChanges();
                return deviceType.Id;
            }
        }

        private string getState(int? state)
        {
            if (state == 1)
            {
                return "正常";
            }
            if (state == 2)
            {
                return "维修中";
            }
            if (state == 3)
            {
                return "已报废";
            }
            if (state == 4)
            {
                return "已外借";
            }
            if (state == 5)
            {
                return "已损坏";
            }
            if (state == 6)
            {
                return "已删除";
            }
            return "";

        }

        public IActionResult PrintTag(string id)
        {
            var d = _context.Device.Where(p => p.Id == id).Single();
            return View(d);
        }


        /// <summary>
        /// 下载导入模板
        /// </summary>
        /// <returns></returns>
        public IActionResult DownloadTemplate()
        {
            string sWebRootFolder = GetTuple().Item1;
            string sFileName = GetTuple().Item2;
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
                //添加头
                worksheet.Cells[1, 1].Value = "设备名称";
                worksheet.Cells[1, 2].Value = "设备类型";
                worksheet.Cells[1, 3].Value = "规格型号";
                worksheet.Cells[1, 4].Value = "设备编号";
                worksheet.Cells[1, 5].Value = "固定资产编号";
                worksheet.Cells[1, 6].Value = "所在机房";                
                worksheet.Cells[1, 7].Value = "采购日期";
                worksheet.Cells[1, 8].Value = "接管日期";
                worksheet.Cells[1, 9].Value = "出保日期";
                worksheet.Cells[1, 10].Value = "摆放位置";
                worksheet.Cells[1, 11].Value = "所属部门";
                worksheet.Cells[1, 12].Value = "供应商";
                worksheet.Cells[1, 13].Value = "IP地址";
                worksheet.Cells[1, 14].Value = "操作系统";


                //设置宽度
                for(int i = 1; i < 14; i++)
                {
                    worksheet.Column(i).Width = 35;
                }

                int row = 2;
                worksheet.Cells[2, 1].Value = "请输入设备名称";
                worksheet.Cells[row, 2].Value = "请输入设备类型";
                worksheet.Cells[row, 3].Value = "请输入设备型号";
                worksheet.Cells[row, 4].Value = "请输入设备编号";
                worksheet.Cells[row, 5].Value = "请输入固定资产编号";
                worksheet.Cells[row, 6].Value = "";
                worksheet.Cells[row, 7].Value = "格式：2017-08-05";
                worksheet.Cells[row, 8].Value = "格式：2017-08-05";
                worksheet.Cells[row, 9].Value = "格式：2017-08-05";
                worksheet.Cells[row, 10].Value = "请输入摆放位置";
                worksheet.Cells[row, 11].Value = "";
                worksheet.Cells[row, 12].Value = "";
                worksheet.Cells[row, 13].Value = "请输入IP地址";
                worksheet.Cells[row, 14].Value = "请输入操作系统";

                package.Save();
            }
            return File("tmp/" + sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "设备导入模板.xlsx");
        }

        /// <summary>
        /// 获取设备类型列表，根据分类
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        public List<DeviceType> typeList(String category)
        {
            var q = this._context.DeviceType.Where(p => p.Deleted == 0); 
            if (!string.IsNullOrWhiteSpace(category))
            {
                q = q.Where(p => p.DeviceTypeCategoryId == category);
            }
            q = q.OrderBy(p => p.SortOrder);

            return q.ToList();
        }

    }    
}